This script evaluates an alternative method of defining minimum part-load ("Equipment SEER/IEER") and full-load ("Equipment EER") efficiency levels. The efficiency levels determine if a given air conditioning "DX Unit" is qualified for a prescriptive rebate in electrical energy efficiency programs.
The script uses data from the "Large Unitary" equipment category of the AHRI Certification Directory to represent the DX Unit market. The analysis estimates how changes in minimum qualifying efficiency levels impact the total quantity of certified equipment that exceed the minimum qualifications. The results from the analysis will be used as guidance in forecasting participation in the MN HVAC-R prescriptive cooling program if the "Flexible Minimum Qualifying" concept is implemented
Matt Chmielewski
https://github.com/emskiphoto
July 27, 2021
Under this example efficiency program, the net economical benefit of electrical energy savings to the utility is a two-dimensional question. First, electrical demand (kW) savings benefits are determined by full-load equipment efficiency. Second, electrical consumption (kWh) savings are determined by part-load efficiency. Finally, participating equipment must meet mininimum efficiency levels set for each capacity range.
Cooling equipment has seen significant improvements in part-load efficiency over recent years, whereas full-load efficiencies have improved little. This has created a scenario where equipment with excellent part-load performance does not qualify for any incentives because it fails to meet full-load minimum efficiences by a small margin.
The current opportunity is to evaluate a 'flexible' minimum qualifying values that accept variable part-load and full-load efficiencies instead of fixed values by size range. The benefit is expected to be greater program participation due to more qualified equipment and subsequently greater energy savings claims. The limit of optimization is defined by the net benefit to the utility - the cost of the more generous minimum qualifying levels must not be low enough to avoid paying more for the efficiency benefits than their expected value to the utility.
AHRI "Large Unitary" - https://www.ahridirectory.org/NewSearch?programId=3&searchTypeId=3&productTypeId=3401. This is considered to be a proxy for the Energy Efficiency Program market and is a best estimate of actual market offering. This assumption has not been validated in any way. This study should be further developed to include:
1. Local cooling market surveys
2. Exclusion of brands known to not distribute product in the local market<BR>
DX Units Opportunity History - Salesforce
DX Units Calculation Parameters - Salesforce Calculation Parameters
import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
import datetime as dt
import xcel_utils as xu
%config Completer.use_jedi = False
sns.set_context('notebook')
plt.rcParams['font.size']= 12
file_AHRI = 'data/AHRI Unitary Large complete.csv'
df = pd.read_csv(file_AHRI, index_col=0)
display(df.shape)
df.head()
(14046, 10)
| AHRI Certified Reference Number | Model Status | Manufacturer | Model Number | Indoor Unit Model Number | AHRI Type | unit cooling BTUH | Equipment EER | Equipment SEER/IEER | Full Load Indoor Coil Air Quantity (scfm) | |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 3243059 | Active | rheem | RAWL-150D | RHGL-180Z | RCU-A-CB | 146000.0 | 11.10 | 14.6 | 5000.0 |
| 1 | 3243060 | Active | rheem | RAWL-150Y | RHGL-180Y | RCU-A-CB | 146000.0 | 10.18 | 12.0 | 5000.0 |
| 2 | 3243064 | Active | rheem | RAWL-240C | RHGL-240Z | RCU-A-CB | 224000.0 | 8.99 | 11.6 | 6900.0 |
| 3 | 3243065 | Active | rheem | RAWL-240D | RHGL-240Z | RCU-A-CB | 214000.0 | 8.99 | 11.6 | 6900.0 |
| 4 | 3243066 | Active | rheem | RAWL-240Y | RHGL-240Y | RCU-A-CB | 240000.0 | 10.00 | 11.6 | 6900.0 |
# df.info()
# top_10_mfrs = [x[:15] for x in df.Manufacturer.value_counts().index[:10]]
top_10_mfrs = df.Manufacturer.value_counts().index[:10]
plt.figure(figsize=(8,8))
df.plot(kind='scatter', x='Equipment EER', y='Equipment SEER/IEER', alpha=0.2, marker='.', color='blue')
plt.title('EER vs SEER values for all AHRI DX Units')
plt.grid(axis='both')
plt.show()
C:\Users\237525\Miniconda3\lib\site-packages\pandas\plotting\_matplotlib\core.py:1070: UserWarning: No data for colormapping provided via 'c'. Parameters 'cmap' will be ignored scatter = ax.scatter(
<Figure size 800x800 with 0 Axes>
As of 4/16/2021 the AHRI Large Unitary Database has 17,863 entries but the file used for analysis has 14,046 entries. The analysis file was first created in 2019. Ultimately, changes in total qualified equipment counts will be reported in terms of percentages and not absolute counts. However, it must be acknowledged that not having the most recent 18+ months of data may underestimate the quantity of newer, and presumably higher efficiency equipment.
This data set is already 100% Active equipment (as of 2019)
df['Model Status'].value_counts()
Active 14046 Name: Model Status, dtype: int64
df = df.loc[df['Model Status'] == 'Active']
12,000 BTUH = 1 Ton
df['Unit Size (Tons)'] = df['unit cooling BTUH'].apply(lambda x: x/12000)
df[['Unit Size (Tons)', 'unit cooling BTUH']].sample(3)
| Unit Size (Tons) | unit cooling BTUH | |
|---|---|---|
| 133 | 6.000000 | 72000.0 |
| 40 | 25.666667 | 308000.0 |
| 37 | 31.666667 | 380000.0 |
These are the Energy Efficiency levels that determine qualification for the program. These values are constant within a given size range.
file_equipment = 'data\Cooling Minimum Efficiencies by Size.xlsx'
df_equipment = pd.read_excel(file_equipment, sheet_name='one_table').dropna(axis=1)
labels = list(df_equipment[['eo3__Equipment_Type__c']].values[:,0])
df_equipment[['eo3__Equipment_Type__c','Size_Min__c','Size_Max__c','Min_SEER__c','Min_EER__c']]
| eo3__Equipment_Type__c | Size_Min__c | Size_Max__c | Min_SEER__c | Min_EER__c | |
|---|---|---|---|---|---|
| 0 | MN - RTUS < 5.42 tons | 0.00 | 5.42 | 13.7 | 11.05 |
| 1 | MN - RTUS - 5.42 - 11.24 tons | 5.42 | 11.25 | 12.2 | 11.30 |
| 2 | MN - RTUS - 11.25 - 19.99 tons | 11.25 | 20.00 | 12.1 | 11.10 |
| 3 | MN - RTUS - 20 - 63.33 tons | 20.00 | 63.34 | 12.0 | 10.90 |
| 4 | MN - RTUS - 63.34 tons and above | 63.34 | 300.00 | 12.0 | 10.90 |
size_ranges = df_equipment[['Size_Min__c','Size_Max__c']].to_numpy()
bin_ranges = [tuple(size_ranges[i]) for i in range(0,len(size_ranges))]
# bin_ranges
bins = pd.IntervalIndex.from_tuples(bin_ranges)
df['size_range'] = pd.cut(df['Unit Size (Tons)'].to_list(), bins, labels=labels)
df['dx_units_category'] = df['size_range'].map(dict(zip(df['size_range'].unique()\
.sort_values(),labels[:-1])))
# df['dx_units_category'].value_counts().loc[labels[:-1]].plot(kind='bar')
# plt.title('AHRI equipment count by size range')
# plt.show()
# df[['Unit Size (Tons)','dx_units_category','size_range','Equipment EER','Equipment SEER/IEER']].sample(5)
min_base_eff_cols = ['EER_Baseline__c','SEER_Baseline__c',
'Min_EER__c','Min_SEER__c','Incremental_Cost_per_Ton__c']
df = pd.merge(left=df, right=df_equipment[min_base_eff_cols + ['eo3__Equipment_Type__c']],
left_on='dx_units_category', right_on='eo3__Equipment_Type__c')
df[['Unit Size (Tons)','dx_units_category',
'Equipment EER','Equipment SEER/IEER'] + min_base_eff_cols].sample(5)
| Unit Size (Tons) | dx_units_category | Equipment EER | Equipment SEER/IEER | EER_Baseline__c | SEER_Baseline__c | Min_EER__c | Min_SEER__c | Incremental_Cost_per_Ton__c | |
|---|---|---|---|---|---|---|---|---|---|
| 7684 | 36.666667 | MN - RTUS - 20 - 63.33 tons | 10.7 | 14.6 | 9.8 | 9.9 | 10.9 | 12.0 | 189 |
| 5581 | 30.666667 | MN - RTUS - 20 - 63.33 tons | 10.0 | 11.7 | 9.8 | 9.9 | 10.9 | 12.0 | 189 |
| 1260 | 14.666667 | MN - RTUS - 11.25 - 19.99 tons | 12.4 | 13.0 | 10.8 | 11.0 | 11.1 | 12.1 | 140 |
| 11709 | 7.166667 | MN - RTUS - 5.42 - 11.24 tons | 11.3 | 12.4 | 11.0 | 11.2 | 11.3 | 12.2 | 380 |
| 4062 | 20.166667 | MN - RTUS - 20 - 63.33 tons | 11.0 | 12.0 | 9.8 | 9.9 | 10.9 | 12.0 | 189 |
qualification_status = ['qualified_EER', 'qualified_SEER', 'qualified_(standard_criteria)']
df['qualified_EER'] = df['Equipment EER'] >= df['Min_EER__c']
df['qualified_SEER'] = df['Equipment SEER/IEER'] >= df['Min_SEER__c']
df['qualified_(standard_criteria)'] = df['qualified_EER'] & df['qualified_SEER']
# df[['dx_units_category','Equipment EER','Equipment SEER/IEER'] + min_base_eff_cols + qualification_status].sample(5).T
# df.groupby('dx_units_category')['Equipment SEER/IEER'].describe().loc[:,['mean', 'std', '25%', '50%', '75%']].round(1)
# df.groupby('dx_units_category')['Equipment SEER/IEER'].quantile([0.1,0.9])
df['dx_units_category'].value_counts().loc[labels[:-1][::-1]].plot(kind='barh', color='brown')
plt.title('AHRI equipment count by size range')
plt.grid(axis='x')
plt.xlabel('Equipment Quantity')
plt.yticks(ticks=range(-1,4), labels=labels[::-1])
plt.annotate('No AHRI Data',xy=(1000,2.8),xycoords='data',xytext=(1000,2.8))
plt.annotate('No AHRI Data',xy=(1000,-1),xycoords='data',xytext=(1000,-1))
plt.ylim(-1.5,3.5)
plt.show()
And there is no data from AHRI for this size range to be used in this analysis. https://www.ahrinet.org/App_Content/ahri/files/Certification/OM%20pdfs/ULE_OM.pdf
And there is no data from AHRI for this size range to be used in this analysis. https://www.ahrinet.org/App_Content/ahri/files/Certification/OM%20pdfs/ULE_OM.pdf
df_EER_SEER_melt = df[['Equipment SEER/IEER','Equipment EER',
'dx_units_category']].melt(id_vars='dx_units_category',
value_name='EER or SEER')
plt.figure(figsize=(10,7))
sns.boxplot(data=df_EER_SEER_melt, y='dx_units_category', x='EER or SEER',
hue='variable', hue_order=['Equipment EER','Equipment SEER/IEER'],order=labels)
# sns.swarmplot(data=df_EER_SEER_melt, y='dx_units_category', x='EER or SEER',
# hue='variable', hue_order=['Equipment EER','Equipment SEER/IEER'],order=labels)
plt.grid(axis='x')
plt.title('EER and SEER values by size for all AHRI certified DX Units')
plt.legend(loc='lower right')
plt.show()
History will be used to understand distributions and subsequent weightings of "Building Type" and Equivalent Full-Load Hours (EFLH)
file_history = 'data/Cooling Incentive History.csv'
df_history = pd.read_csv(file_history)
df_history.head(3)
| F_Measure_Type_Name__c | F_Equipment_Name_Proposed__c | I_EER_MC04__c | P_Cooling_Hrs_mc4__c | i_Qty_Prop_Equip_mc__c | i_SEER_MC04__c | i_unit_cooling_BTUH__c | i_unit_cooling_tons_mc__c | County__c | Customer_Therms__c | ... | Uncapped_Electric_Rebate__c | Uncapped_Gas_Rebate__c | Units_Electric__c | Units_Gas__c | county__c | eo3__Completion_Date__c | eo3__Eligible_Equipment_Type_1__c | AF_Date__c | Name | i_Facility_Type__c | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | Cool EER-SEER | MN - RTUS - 5.42 - 11.24 tons | 12.8 | 563 | 2 | 19.1 | NaN | 7.42 | NaN | NaN | ... | 4704.28 | 0.0 | 2 | 0 | Hennepin MN3 | 2019-09-13 | NaN | 2021-01-13 | OID4327679-0002 | Education - Secondary |
| 1 | Cool EER-SEER | MN - RTUS - 5.42 - 11.24 tons | 12.0 | 563 | 1 | 13.0 | NaN | 7.42 | NaN | NaN | ... | 756.84 | 0.0 | 1 | 0 | Hennepin MN3 | 2019-09-13 | NaN | 2021-01-13 | OID4327679-0003 | Education - Secondary |
| 2 | Cool SEER - MN | MN - RTUS < 5.42 tons | 12.0 | 563 | 1 | 16.0 | NaN | 3.92 | NaN | NaN | ... | 466.48 | 0.0 | 1 | 0 | Hennepin MN3 | 2019-09-13 | Cool EER-SEER | 2021-01-13 | OID4327679-0001 | Education - Secondary |
3 rows × 77 columns
df_history['AF_Year'] = pd.to_datetime(df_history['AF_Date__c']).dt.year
df_history[['Name','F_Equipment_Name_Proposed__c','i_SEER_MC04__c','I_EER_MC04__c',
'i_unit_cooling_tons_mc__c','Customer_kW__c','Customer_kWh__c']].head(5)
| Name | F_Equipment_Name_Proposed__c | i_SEER_MC04__c | I_EER_MC04__c | i_unit_cooling_tons_mc__c | Customer_kW__c | Customer_kWh__c | |
|---|---|---|---|---|---|---|---|
| 0 | OID4327679-0002 | MN - RTUS - 5.42 - 11.24 tons | 19.1 | 12.8 | 7.42 | 2.277 | 3703 |
| 1 | OID4327679-0003 | MN - RTUS - 5.42 - 11.24 tons | 13.0 | 12.0 | 7.42 | 0.675 | 620 |
| 2 | OID4327679-0001 | MN - RTUS < 5.42 tons | 16.0 | 12.0 | 3.92 | 0.337 | 382 |
| 3 | OID4484533-0002 | MN - RTUS - 11.25 - 19.99 tons | 13.0 | 12.0 | 16.50 | 3.667 | 3611 |
| 4 | OID4484533-0001 | MN - RTUS - 20 - 63.33 tons | 12.5 | 11.6 | 25.00 | 4.750 | 4110 |
Reduce history to only relevant & matching records
(df_history['i_Facility_Type__c'] == df_history['F_Equip_Building_Type__c']).value_counts()
True 4099 dtype: int64
df_history['F_Equipment_Name_Proposed__c'].isin(df_equipment['eo3__Equipment_Type__c']).value_counts()
True 4099 Name: F_Equipment_Name_Proposed__c, dtype: int64
Fill NaN values in I_EER using this conversion: 𝐸𝐸𝑅=𝑆𝐸𝐸𝑅×0.85
df_history.I_EER_MC04__c.isna().value_counts()
False 3611 True 488 Name: I_EER_MC04__c, dtype: int64
df_history.I_EER_MC04__c.fillna(df_history.i_SEER_MC04__c.apply(lambda x: x*0.85), inplace=True)
df_history.I_EER_MC04__c.isna().value_counts()
False 4099 Name: I_EER_MC04__c, dtype: int64
df_history[['Customer_kW__c','F_Net_Gen_kW__c','Customer_kWh__c','F_Net_Gen_kWh__c']].sample(5)
| Customer_kW__c | F_Net_Gen_kW__c | Customer_kWh__c | F_Net_Gen_kWh__c | |
|---|---|---|---|---|
| 2142 | 0.603 | 0.58387 | 285 | 305.14 |
| 3251 | 1.611 | 1.55290 | 1433 | 1545.00 |
| 3278 | 1.611 | 1.55290 | 25661 | 27666.62 |
| 589 | 27.844 | 26.94516 | 43752 | 46843.68 |
| 3019 | 0.494 | 0.47742 | 644 | 689.51 |
EFLH is the total deemed full-load operating hours for a given facility type and climate.
file_parameters = 'data/Cooling Incentive Parameters.csv'
df_calc_params = pd.read_csv(file_parameters)
df_calc_params.head(2)
| Name | eo3__Type__c | eo3__Value__c | External_Identifier__c | Application__c | Central_Air_in_Home__c | Cooling_Type__c | Equipment_Category__c | Existing_Insulation__c | Facility_Type__c | ... | State__c | Year__c | Zone__c | CreatedDate | eo3__Valid_From__c | eo3__Valid_To__c | eo3__Search_Pairs__c | eo3__Measure_Type__c | Unnamed: 21 | County_list | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | B03_2 - Cooling | Cooling Hours | 647 | P_Cooling_Hrs_mc4__c | NaN | False | NaN | NaN | False | Convenience Store | ... | MN | NaN | MN1 | 2017-02-04T16:13:38.000+0000 | 2017-01-01 | 2029-12-31 | F_Equip_Building_Type__c=Facility_Type__c,F_Co... | Cooling Centrifugal v3,Cooling Centrifugal v3-... | NaN | Aitkin MN2 |
| 1 | B03_2 - Cooling | Cooling Hours | 986 | P_Cooling_Hrs_mc4__c | NaN | False | NaN | NaN | False | Convenience Store | ... | MN | NaN | MN3 | 2017-02-04T16:13:38.000+0000 | 2017-01-01 | 2029-12-31 | F_Equip_Building_Type__c=Facility_Type__c,F_Co... | Cooling Centrifugal v3,Cooling Centrifugal v3-... | NaN | Anoka MN3 |
2 rows × 23 columns
df_EFLH_MN3 = df_calc_params.loc[(df_calc_params['eo3__Type__c'] == 'Cooling Hours') &
(df_calc_params['Zone__c'] == 'MN3'),
['Facility_Type__c','eo3__Value__c','Zone__c']]\
.set_index('Facility_Type__c', drop=True)
df_EFLH_MN3.head(5)
| eo3__Value__c | Zone__c | |
|---|---|---|
| Facility_Type__c | ||
| Convenience Store | 986 | MN3 |
| Data Center | 8,760 | MN3 |
| Education - Community College/University | 785 | MN3 |
| Education - Primary | 408 | MN3 |
| Education - Secondary | 563 | MN3 |
Cooling only
DX = Direct Expansion (heat is transferred 'directly' from refrigerant to air - no intermediate fluid)
EER = Full load cooling efficiency (higher number = more efficient)
SEER (IEER) = Part-load cooling efficiency (higher number = more efficient)
Tons = Cooling Capacity (equal to 12,000 BTUH) A typical MN home is 1-2 tons of capacity
EFLH = Equivalent Full Load Hours (cooling hours per year)
EER/SEER Baseline = Minimum Efficiency permitted by building code
Minimum Qualifying EER/SEER = Arbitrary minimum qualifying levels set by utility
Avoided Revenue Requirement (ARR) = Monetary benefit to Utility derived from projected savings
plt.figure(figsize=(10,10))
df.plot(kind='scatter', x='Equipment EER', y='Equipment SEER/IEER', alpha=0.2,
marker='.', color='blue')
plt.title('EER vs SEER values for all AHRI DX Units')
plt.xlabel('EER - Full Load Efficiency = kW savings -->>', fontsize=15)
plt.ylabel('SEER - Part Load Efficiency = kWh savings -->>', fontsize=15)
plt.gca().xaxis.set_major_locator(plt.NullLocator())
plt.gca().yaxis.set_major_locator(plt.NullLocator())
plt.grid(axis='both')
plt.show()
C:\Users\237525\Miniconda3\lib\site-packages\pandas\plotting\_matplotlib\core.py:1070: UserWarning: No data for colormapping provided via 'c'. Parameters 'cmap' will be ignored scatter = ax.scatter(
<Figure size 1000x1000 with 0 Axes>
sns.set_context('talk')
product = "MN - HVAC+R Cooling - Prescriptive"
before_2021 = df_history['AF_Year'] < 2021
df_performance_by_year = df_history.loc[before_2021].groupby(
by=['AF_Year','F_Equipment_Name_Proposed__c']).agg({'Name':'count',
'I_Qty_Prop_Equip__c':sum,
'i_unit_cooling_tons_mc__c':'mean',
'I_EER_MC04__c':'mean',
'i_SEER_MC04__c':'mean',
'P_Cooling_Hrs_mc4__c':'mean',
'Customer_kWh__c':sum,
'Customer_kW__c':sum
})
df_performance_by_year.rename(columns={'Name':'Participants'}, inplace=True)
# df_performance_by_year
savings_metrics = ['Customer_kWh__c','Customer_kW__c','Participants']
df_perf_year_melt = df_performance_by_year.reset_index()
fig, [ax1, ax2] = plt.subplots(1,2,figsize=(10,5))
sns.barplot(data=df_perf_year_melt.groupby('AF_Year').sum().reset_index(),
x='AF_Year',y='Customer_kW__c', ax=ax1, palette="viridis",)
sns.barplot(data=df_perf_year_melt.groupby('AF_Year').sum().reset_index(),
x='AF_Year',y='Customer_kWh__c', ax=ax2, palette="mako")
ax1.grid(axis='y')
ax2.grid(axis='y')
plt.suptitle('Annual DX Units kW & kWh savings', y=1.1)
plt.show()
sns.set_context('notebook')
for savings_category in savings_metrics:
plt.figure(figsize=(12,2))
plt.title(f'{savings_category}')
sns.barplot(data=df_perf_year_melt, x='AF_Year',y=f'{savings_category}', hue='F_Equipment_Name_Proposed__c', hue_order=labels )
plt.grid(axis='y')
plt.legend(loc='upper left')
fig.suptitle(f'Annual Achievement for DX Units in {product}')
plt.show()
df_history_summary = df_history.groupby('F_Equipment_Name_Proposed__c').agg({'Name':'count',
'I_Qty_Prop_Equip__c':sum,
'i_unit_cooling_tons_mc__c':'mean',
'I_EER_MC04__c':'mean',
'i_SEER_MC04__c':'mean',
'P_Cooling_Hrs_mc4__c':'mean'
}).loc[labels]
df_history_summary.rename(columns={'Name':'Participants'}, inplace=True)
# df_history_summary
def style_false(v, props=''):
return props if v == False else None
def style_true(v, props=''):
return props if v == True else None
cols = ['Model Number','Unit Size (Tons)','Equipment SEER/IEER','Min_SEER__c','qualified_SEER',
'Equipment EER','Min_EER__c', 'qualified_EER']
df.loc[(df['Equipment SEER/IEER'] > 15) & df['qualified_SEER'] & ~df['qualified_EER'], cols].round(1).sample(3).set_index('Model Number',drop=True).style.applymap(style_false, props='color:red;').applymap(style_true, props='color:green;')
| Unit Size (Tons) | Equipment SEER/IEER | Min_SEER__c | qualified_SEER | Equipment EER | Min_EER__c | qualified_EER | |
|---|---|---|---|---|---|---|---|
| Model Number | |||||||
| DPSA040A2BSAS*0B02500*G | 36.700000 | 15.300000 | 12.000000 | True | 10.600000 | 10.900000 | False |
| RPS063DSE-4-V-6 | 53.800000 | 15.100000 | 12.000000 | True | 10.500000 | 10.900000 | False |
| RPS051DSE-4-V-4-0 | 45.000000 | 15.100000 | 12.000000 | True | 10.200000 | 10.900000 | False |
Which inputs and parameters in the prescriptive measure can be changed?
The flexible minimum qualifying concept increases the range of qualified DX Unit equipment by accepting equipment with lower EERs (full-load efficiency) and higher SEERs (part-load efficiency). The concept uses a single result, Total Electric Avoided Revenue Requirement (ARR), to determine if candidate equipment results in more Total Electric ARR than would occur under the current minimum qualifying rules.
For MN Cooling:
ARR/kW = 1491
ARR/kWh = 0.452
EER and SEER efficiencies have disproportionate impacts on Utility Benefits and must not be considered in isolation.
Total Electric Utility ARR is a function of Customer kW and kWh savings multiplied by net generation and Utility ARR/kW and ARR/kWh multipliers. The Minimum Utility Electric ARR is proposed in this analysis as an alternative minimum qualifying criteria in place of the standard fixed minimum qualifying EER and SEER values. In other words, instead of judging the qualification of a new equipment application based on efficiency levels, the passing criteria will be Minimum Utility Electric ARR. However, the goal is to maintain the appearance and simplicity of using the EER and SEER values, but now those EER and SEER values will be set based on Minimum Utility Electric ARR. The Minimum Utility Electric ARR needs to be determined for each combination of Equipment type, location and Facility Type. Note that due to the overwhelming dominance of historical measures in the 'MN3' location, only this location will be analyzed and MN1, MN2 will be overlooked. Also, Equipment Capacity has a linear relationship with Minimum Utility Electric ARR, so the effect of Equipment capac
def customer_kW_DX(EER_Baseline__c, I_EER_MC04__c, i_unit_cooling_tons_mc__c, I_Qty_Prop_Equip__c):
return round(((12/EER_Baseline__c)-(12/I_EER_MC04__c))*i_unit_cooling_tons_mc__c,3)
customer_kW_DX(11, 13, 5, 1)
0.839
def P_Cooling_Hrs_mc4(i_Facility_Type__c, df=df_EFLH_MN3):
return df.loc[i_Facility_Type__c,'eo3__Value__c']
P_Cooling_Hrs_mc4('Restaurant')
'652'
def customer_kWh_DX(SEER_Baseline__c, i_SEER_MC04__c, i_unit_cooling_tons_mc__c, I_Qty_Prop_Equip__c, P_Cooling_Hrs_mc4__c):
# return ((12/EER_Baseline__c)-(12/I_EER_MC04__c))*i_unit_cooling_tons_mc__c
# return int(round(((12/SEER_Baseline__c)-(12/i_SEER_MC04__c))*i_unit_cooling_tons_mc__c*I_Qty_Prop_Equip__c*P_Cooling_Hrs_mc4__c,0))
return round(((12/SEER_Baseline__c)-(12/i_SEER_MC04__c))*i_unit_cooling_tons_mc__c*I_Qty_Prop_Equip__c*P_Cooling_Hrs_mc4__c,0)
customer_kWh_DX(11,15,10,1,1000)
2909.0
def net_gen_kWh_savings(energy_savings, Customer_to_Net_Gen = 1.01911236580832):
if type(energy_savings) == int:
# response = int(energy_savings/Customer_to_Net_Gen)
response = int(energy_savings*Customer_to_Net_Gen)
else:
# response = round(energy_savings/Customer_to_Net_Gen,3)
response = round(energy_savings*Customer_to_Net_Gen,3)
return response
def net_gen_kW_savings(energy_savings, Customer_to_Net_Gen = 1.01911236580832):
if type(energy_savings) == int:
response = int(energy_savings/Customer_to_Net_Gen)
# response = int(energy_savings*Customer_to_Net_Gen)
else:
response = round(energy_savings/Customer_to_Net_Gen,3)
# response = round(energy_savings*Customer_to_Net_Gen,3)
return response
net_gen_kWh_savings(10.555)
10.757
net_gen_kW_savings(10.555)
10.357
def utility_electric_kW_ARR(net_gen_kW, ARR_kW = 1491.04593569493):
return round(net_gen_kW*ARR_kW,2)
utility_electric_kW_ARR(0.823)
1227.13
def utility_electric_kWh_ARR(net_gen_kWh, ARR_kWh = 0.452115074609045):
return round(net_gen_kWh*ARR_kWh,2)
utility_electric_kWh_ARR(2854)
1290.34
def utility_electric_ARR(utility_electric_kW_ARR,utility_electric_kWh_ARR):
return round(sum([utility_electric_kW_ARR,utility_electric_kWh_ARR]),2)
utility_electric_ARR(1227.131,1290)
2517.13
def utility_ARR(EER_Baseline__c, I_EER_MC04__c,SEER_Baseline__c, i_SEER_MC04__c, i_unit_cooling_tons_mc__c,I_Qty_Prop_Equip__c, P_Cooling_Hrs_mc4__c):
return utility_electric_ARR(utility_electric_kW_ARR(net_gen_kW_savings(customer_kW_DX(EER_Baseline__c, I_EER_MC04__c,i_unit_cooling_tons_mc__c, I_Qty_Prop_Equip__c))),
utility_electric_kWh_ARR(net_gen_kWh_savings(customer_kWh_DX(SEER_Baseline__c, i_SEER_MC04__c, i_unit_cooling_tons_mc__c,I_Qty_Prop_Equip__c, P_Cooling_Hrs_mc4__c))))
utility_ARR(9.8,10,9.9,13.5,40.83,1,622)
5245.07
Objectives:
1. Visualize coincidence of two dimensions<BR>
2. Could there be significant gains in potential participation that don't harm cost-effectiveness?
dx_unit = labels[1:2][0]
fig, [ax1, ax2] = plt.subplots(2,1, sharex=True, figsize = (8,8))
ax1 = sns.histplot(data=df.loc[df['dx_units_category'] == dx_unit], x='Equipment EER', ax=ax1, stat='probability', kde=True)
ax1.grid(axis='x')
ax1.set_title('Distribution of Equipment EER')
plt.suptitle(f'{dx_unit} - AHRI Directory')
ax2 = sns.histplot(data=df.loc[df['dx_units_category'] == dx_unit], x='Equipment SEER/IEER', color='red', ax= ax2, stat='probability', kde=True)
ax2.grid(axis='x')
ax1.set_title('Distribution of Equipment SEER/IEER')
# plt.title(f'{dx_unit}')
plt.xlim(9, 18)
(9.0, 18.0)
sns.set_context('talk')
# for dx_unit in dx_unit[:]:
# SEER_baseline = float(df_equipment.loc[df_equipment['eo3__Equipment_Type__c'] == dx_unit, 'SEER_Baseline__c'] )
# EER_baseline = float(df_equipment.loc[df_equipment['eo3__Equipment_Type__c'] == dx_unit, 'EER_Baseline__c'])
# Min_SEER = df.loc[df['dx_units_category']==dx_unit,'Min_SEER__c'].unique()[0]
# Min_EER = df.loc[df['dx_units_category']==dx_unit,'Min_EER__c'].unique()[0]
sns.displot(data = df.loc[df['dx_units_category'] == dx_unit], x='Equipment EER',
y='Equipment SEER/IEER', kind='kde', height=5, aspect=1.7)
plt.title(f'Distribution of EER vs SEER values for - {dx_unit} in AHRI database')
plt.xlim(9,13)
plt.ylim(9,18)
plt.grid(axis='both')
plt.margins(0.9)
plt.show()
sns.set_context('talk')
# for dx_unit in ['MN - RTUS - 5.42 - 11.24 tons','MN - RTUS - 11.25 - 19.99 tons', 'MN - RTUS - 20 - 63.33 tons'][:1]:
for dx_unit in labels[1:4]:
SEER_baseline = float(df_equipment.loc[df_equipment['eo3__Equipment_Type__c'] == dx_unit, 'SEER_Baseline__c'] )
EER_baseline = float(df_equipment.loc[df_equipment['eo3__Equipment_Type__c'] == dx_unit, 'EER_Baseline__c'])
Min_SEER = df.loc[df['dx_units_category']==dx_unit,'Min_SEER__c'].unique()[0]
Min_EER = df.loc[df['dx_units_category']==dx_unit,'Min_EER__c'].unique()[0]
sns.displot(data = df.loc[df['dx_units_category'] == dx_unit], x='Equipment EER',
y='Equipment SEER/IEER', kind='kde', height=7, aspect=1.7)
plt.hlines(Min_SEER,9,13, linestyle='--', colors='red', label='Min SEER')
plt.vlines(Min_EER,9,18, linestyle='dotted', colors='red', label='Min EER')
plt.hlines(SEER_baseline,9,13, linestyle='--', colors='gray', label='Baseline SEER')
plt.vlines(EER_baseline,9,18, linestyle='dotted', colors='gray', label='Baseline EER')
plt.suptitle(f'Distribution of EER vs SEER values for - {dx_unit} in AHRI database', y=1.05)
plt.axvspan(EER_baseline, Min_EER, ymin=(EER_baseline-9)/(18-9), alpha=0.3, color='yellow')
plt.axhspan(SEER_baseline, Min_SEER, xmin=(SEER_baseline-9)/(13-9), alpha=0.3, color='yellow', label='Code Compliant')
plt.axvspan(Min_EER,13, color='green',ymin=(Min_SEER-9)/(18-9), alpha=0.3, label='Qualified')
# plt.annotate()
plt.annotate('Minimum Qualifed ARR', xy=(Min_EER, Min_SEER), xycoords='data',
xytext=(Min_EER*1.12, Min_SEER*0.85), textcoords='data',
arrowprops=dict(facecolor='black', shrink=0.05),
horizontalalignment='right', verticalalignment='top')
plt.xlim(9,13)
plt.ylim(9,18)
plt.grid(axis='both')
plt.legend(loc='upper left')
plt.margins(0.9)
plt.show()
Equipment models that comply with code but do not meet minimum qualifying criteria
total_models, code_compliant_models, qualified_models = [], [], []
code_compliant_equipment = (df['Equipment EER'] > df['EER_Baseline__c']) & (df['Equipment SEER/IEER'] > df['SEER_Baseline__c'])
qualified_equipment = (df['Equipment EER'] > df['Min_EER__c']) & (df['Equipment SEER/IEER'] > df['Min_SEER__c'])
for dx_unit in labels:
total_models_ = len(df.loc[(df['dx_units_category'] == dx_unit)])
code_compliant_models_ = len(df.loc[code_compliant_equipment & (df['dx_units_category'] == dx_unit)])
qualified_models_ = len(df.loc[qualified_equipment & (df['dx_units_category'] == dx_unit)])
total_models.append(total_models_)
code_compliant_models.append(code_compliant_models_)
qualified_models.append(qualified_models_)
df_qualified_by_model = pd.DataFrame(index=labels, data={'total models':total_models,
'code_compliant_models':code_compliant_models,
'qualified_models':qualified_models})
df_qualified_by_model['potential models'] = df_qualified_by_model['code_compliant_models'] - df_qualified_by_model['qualified_models']
# dictionary of column colors
coldict = {'code_compliant_models':'yellow', 'qualified_models':'green'}
def highlight_cols(s, coldict):
if s.name in coldict.keys():
return ['background-color: {}'.format(coldict[s.name])] * len(s)
return [''] * len(s)
df_qualified_by_model.replace(0,'No Data').style.apply(highlight_cols, coldict=coldict)
| total models | code_compliant_models | qualified_models | potential models | |
|---|---|---|---|---|
| MN - RTUS < 5.42 tons | 5 | 4 | 2 | 2 |
| MN - RTUS - 5.42 - 11.24 tons | 4050 | 3308 | 2315 | 993 |
| MN - RTUS - 11.25 - 19.99 tons | 3672 | 2876 | 2018 | 858 |
| MN - RTUS - 20 - 63.33 tons | 6319 | 6065 | 1479 | 4586 |
| MN - RTUS - 63.34 tons and above | No Data | No Data | No Data | No Data |
df_qualified_by_model[['code_compliant_models','qualified_models']][1:-1].plot(kind='barh', width=0.7)
plt.yticks(np.arange(3),labels[1:-1])
# sns.barplot(data = df_qualified_by_model[1:-1], y='Manufacturer', x='Passing',hue='Criteria', ci=False,order=manufacturer_volume_sort_order[:15])
plt.legend(loc='lower right')
plt.grid(axis='x')
plt.title('AHRI DX Units - Qualified vs Compliant')
plt.show()
Current ARR$/Ton values are significantly different across size ranges - this should be optimized in the future.
a one-dimensional metric for a two dimensional criteria
print('EFLH Historical Statistics\n')
EFLH = df_history.groupby('F_Equipment_Name_Proposed__c')['P_Cooling_Hrs_mc4__c'].describe().loc[:,'50%'].mean()
display(df_history.groupby('F_Equipment_Name_Proposed__c')['P_Cooling_Hrs_mc4__c'].describe().loc[:,['mean','50%']].astype('int'))
print(f'The mean of median EFLH values by Equipment Type is {EFLH}')
EFLH Historical Statistics
| mean | 50% | |
|---|---|---|
| F_Equipment_Name_Proposed__c | ||
| MN - RTUS - 11.25 - 19.99 tons | 786 | 589 |
| MN - RTUS - 20 - 63.33 tons | 820 | 589 |
| MN - RTUS - 5.42 - 11.24 tons | 621 | 588 |
| MN - RTUS - 63.34 tons and above | 1089 | 652 |
| MN - RTUS < 5.42 tons | 600 | 574 |
The mean of median EFLH values by Equipment Type is 598.5
cols = ['EER_Baseline__c','Equipment EER','SEER_Baseline__c','Equipment SEER/IEER',
'Unit Size (Tons)']
df[cols].iloc[8600:8602]
| EER_Baseline__c | Equipment EER | SEER_Baseline__c | Equipment SEER/IEER | Unit Size (Tons) | |
|---|---|---|---|---|---|
| 8600 | 9.8 | 10.0 | 9.9 | 13.5 | 40.833333 |
| 8601 | 9.8 | 10.0 | 9.9 | 13.5 | 40.833333 |
utility_ARR(9.8,10,9.9,13.5,40.83,1,EFLH)
5102.23
df['one'] = 1
df['P_Cooling_Hrs_mc4__c'] = EFLH
df['ARR'] = df.apply(lambda x: utility_ARR(x['EER_Baseline__c'], x['Equipment EER'], x['SEER_Baseline__c'], x['Equipment SEER/IEER'], x['Unit Size (Tons)'], x['one'], x['P_Cooling_Hrs_mc4__c']), axis=1)
df['ARR/Ton'] = df['ARR']/df['Unit Size (Tons)']
df[['P_Cooling_Hrs_mc4__c','ARR','ARR/Ton','Equipment SEER/IEER','Equipment EER','eo3__Equipment_Type__c']]
| P_Cooling_Hrs_mc4__c | ARR | ARR/Ton | Equipment SEER/IEER | Equipment EER | eo3__Equipment_Type__c | |
|---|---|---|---|---|---|---|
| 0 | 598.5 | 1436.41 | 118.061096 | 14.6 | 11.10 | MN - RTUS - 11.25 - 19.99 tons |
| 1 | 598.5 | -899.75 | -73.952055 | 12.0 | 10.18 | MN - RTUS - 11.25 - 19.99 tons |
| 2 | 598.5 | -5820.03 | -311.787321 | 11.6 | 8.99 | MN - RTUS - 11.25 - 19.99 tons |
| 3 | 598.5 | -5558.57 | -311.695514 | 11.6 | 8.99 | MN - RTUS - 11.25 - 19.99 tons |
| 4 | 598.5 | -2290.87 | -114.543500 | 11.6 | 10.00 | MN - RTUS - 11.25 - 19.99 tons |
| ... | ... | ... | ... | ... | ... | ... |
| 14041 | 598.5 | -572.59 | -264.272308 | 9.6 | 9.96 | MN - RTUS < 5.42 tons |
| 14042 | 598.5 | 167.44 | 30.912000 | 13.5 | 11.20 | MN - RTUS < 5.42 tons |
| 14043 | 598.5 | 167.44 | 30.912000 | 13.5 | 11.20 | MN - RTUS < 5.42 tons |
| 14044 | 598.5 | 462.31 | 85.349538 | 14.3 | 11.50 | MN - RTUS < 5.42 tons |
| 14045 | 598.5 | 603.96 | 111.500308 | 14.3 | 11.70 | MN - RTUS < 5.42 tons |
14046 rows × 6 columns
df_equipment[['eo3__Equipment_Type__c','Size_Min__c','Size_Max__c','Min_SEER__c','SEER_Baseline__c','Min_EER__c','EER_Baseline__c']]
| eo3__Equipment_Type__c | Size_Min__c | Size_Max__c | Min_SEER__c | SEER_Baseline__c | Min_EER__c | EER_Baseline__c | |
|---|---|---|---|---|---|---|---|
| 0 | MN - RTUS < 5.42 tons | 0.00 | 5.42 | 13.7 | 13.0 | 11.05 | 11.05 |
| 1 | MN - RTUS - 5.42 - 11.24 tons | 5.42 | 11.25 | 12.2 | 11.2 | 11.30 | 11.00 |
| 2 | MN - RTUS - 11.25 - 19.99 tons | 11.25 | 20.00 | 12.1 | 11.0 | 11.10 | 10.80 |
| 3 | MN - RTUS - 20 - 63.33 tons | 20.00 | 63.34 | 12.0 | 9.9 | 10.90 | 9.80 |
| 4 | MN - RTUS - 63.34 tons and above | 63.34 | 300.00 | 12.0 | 9.6 | 10.90 | 9.50 |
df_equipment['Min_to_Baseline_delta_SEER'] = df_equipment['Min_SEER__c'] - df_equipment['SEER_Baseline__c']
df_equipment['Min_to_Baseline_delta_EER'] = df_equipment['Min_EER__c'] - df_equipment['EER_Baseline__c']
df_equipment['ARR/Ton'] = df_equipment.apply(lambda x: round(utility_ARR(x['EER_Baseline__c'], x['Min_EER__c'], x['SEER_Baseline__c'], x['Min_SEER__c'], x['Size_Max__c'], 1, EFLH)/x['Size_Max__c'],2), axis=1)
# df_equipment['ARR/Ton'] = df_equipment.apply(lambda x: utility_ARR(x['EER_Baseline__c'], x['Min_EER__c'], x['SEER_Baseline__c'], x['Min_SEER__c'], x['Size_Max__c'], x['one'], x['P_Cooling_Hrs_mc4__c']), axis=1)
df_equipment[['eo3__Equipment_Type__c','ARR/Ton','Min_to_Baseline_delta_SEER', 'Min_SEER__c','SEER_Baseline__c','Min_to_Baseline_delta_EER','Min_EER__c','EER_Baseline__c','Size_Min__c','Size_Max__c']]
| eo3__Equipment_Type__c | ARR/Ton | Min_to_Baseline_delta_SEER | Min_SEER__c | SEER_Baseline__c | Min_to_Baseline_delta_EER | Min_EER__c | EER_Baseline__c | Size_Min__c | Size_Max__c | |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | MN - RTUS < 5.42 tons | 13.01 | 0.7 | 13.7 | 13.0 | 0.0 | 11.05 | 11.05 | 0.00 | 5.42 |
| 1 | MN - RTUS - 5.42 - 11.24 tons | 66.62 | 1.0 | 12.2 | 11.2 | 0.3 | 11.30 | 11.00 | 5.42 | 11.25 |
| 2 | MN - RTUS - 11.25 - 19.99 tons | 71.33 | 1.1 | 12.1 | 11.0 | 0.3 | 11.10 | 10.80 | 11.25 | 20.00 |
| 3 | MN - RTUS - 20 - 63.33 tons | 239.28 | 2.1 | 12.0 | 9.9 | 1.1 | 10.90 | 9.80 | 20.00 | 63.34 |
| 4 | MN - RTUS - 63.34 tons and above | 306.31 | 2.4 | 12.0 | 9.6 | 1.4 | 10.90 | 9.50 | 63.34 | 300.00 |
Currently the standard criteria allow very low ARR/ton values with small equipment and ask for very high values from larger equipment. This is driven by the delta between the Minimum Qualifying EER & SEER and the Baseline EER & SEER. There is perfect correlation between ARR/Ton and the deltas. In short, the minimum acceptable delta is much higher on the larger equipment than in the smaller equipment.
This is relevant because I am using the current ARR/Ton values to set the minimum qualifying EER & SEER values in the new flexible minimum qualifying table. This means at the moment the table is perpetuating this disparity between small and large equipment ranges.
If we can (per regulatory guidance) change this I would recommend that we get the minimum ARR/Ton values to be more similar across size ranges.
# df_equipment[['eo3__Equipment_Type__c','ARR/Ton','Min_to_Baseline_delta_SEER','Min_to_Baseline_delta_EER']].corr().iloc[0,1:].round(4)
# df['ARR/Ton'].plot(kind='hist', bins=40)
# sns.scatterplot(data=df,x='Equipment EER', y='Equipment SEER/IEER', size='ARR', alpha=0.5, hue='dx_units_category')
This curve establishes the line that divides qualified and unqualified EER & SEER combinations for each equipment type. An Excel-based tool was created to facilitate creation of the curve and subsequent input in to Salesforce calculators. These values could be determined in this Python script but instead are imported from the Excel-based tool to ensure consistency with the parameters implemented in the Salesforce Production environment.
file_calculator = 'data/Flexible Qualifying Calculator.xlsx'
df_boundary = pd.read_excel(file_calculator, sheet_name='Calculator')
# df_boundary.info()
df_boundary = xu.locate_table_by_header(df_boundary, header='Derive Coefficients for Salesforce Calculator Build',
rows_after_header=15)
df_boundary.head(10)
| Objective | Unnamed: 1 | Unnamed: 2 | Unnamed: 3 | Unnamed: 4 | Unnamed: 5 | Unnamed: 6 | Unnamed: 7 | Unnamed: 8 | Unnamed: 9 | ... | Unnamed: 15 | Unnamed: 16 | Unnamed: 17 | Unnamed: 18 | Unnamed: 19 | Unnamed: 20 | Unnamed: 21 | Unnamed: 22 | Unnamed: 23 | Unnamed: 24 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | Derive Coefficients for Salesforce Calculator ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 1 | eo3__Equipment_Type__c | MN - RTUS < 5.42 tons | MN - RTUS - 5.42 - 11.24 tons | MN - RTUS - 11.25 - 19.99 tons | MN - RTUS - 20 - 63.33 tons | MN - RTUS - 63.34 tons and above | NaN | NaN | NaN | NaN | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 2 | Formula - ARR/Ton as a function of EER: | =1/(-((B$50/(12*Calculator!Net_Gen_to_Customer... | =1/(-((C$50/(12*Calculator!Net_Gen_to_Customer... | =1/(-((D$50/(12*Calculator!Net_Gen_to_Customer... | =1/(-((E$50/(12*Calculator!Net_Gen_to_Customer... | =1/(-((F$50/(12*Calculator!Net_Gen_to_Customer... | NaN | NaN | NaN | NaN | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 3 | ARR/Ton as a function of EER: | -24.634197 | -23.443816 | -30.12354 | -42.754108 | -42.751889 | NaN | NaN | NaN | NaN | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 4 | NaN | -24.634197 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 5 | iCoef0 | 1.063555 | 5.579859 | 5.969913 | 20.137188 | 25.796314 | NaN | NaN | NaN | NaN | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 6 | iCoef1 | 134.936284 | 135.549631 | 138.059809 | 152.147544 | 156.952204 | NaN | NaN | NaN | NaN | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 7 | iCoef2 | 270.590872 | 270.590872 | 270.590872 | 270.590872 | 270.590872 | NaN | NaN | NaN | NaN | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 8 | iCoef3 | 0.076923 | 0.089286 | 0.090909 | 0.10101 | 0.104167 | NaN | NaN | NaN | NaN | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 9 | iCoef4 | 1491.045936 | 1491.045936 | 1491.045936 | 1491.045936 | 1491.045936 | NaN | NaN | NaN | NaN | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
10 rows × 25 columns
columns_idx = list(df_boundary.iloc[:,0] == "eo3__Equipment_Type__c").index(True)
# Set table column names to match eo3__Equipment_Type__c names
df_boundary.columns = df_boundary.iloc[columns_idx,:].values
# df_boundary
coefficients = df_boundary['eo3__Equipment_Type__c'].str.startswith('iC').fillna(False)
df_boundary.loc[coefficients,:].dropna(axis=1)
| eo3__Equipment_Type__c | MN - RTUS < 5.42 tons | MN - RTUS - 5.42 - 11.24 tons | MN - RTUS - 11.25 - 19.99 tons | MN - RTUS - 20 - 63.33 tons | MN - RTUS - 63.34 tons and above | |
|---|---|---|---|---|---|---|
| 5 | iCoef0 | 1.063555 | 5.579859 | 5.969913 | 20.137188 | 25.796314 |
| 6 | iCoef1 | 134.936284 | 135.549631 | 138.059809 | 152.147544 | 156.952204 |
| 7 | iCoef2 | 270.590872 | 270.590872 | 270.590872 | 270.590872 | 270.590872 |
| 8 | iCoef3 | 0.076923 | 0.089286 | 0.090909 | 0.10101 | 0.104167 |
| 9 | iCoef4 | 1491.045936 | 1491.045936 | 1491.045936 | 1491.045936 | 1491.045936 |
# NEW 7-07-21
def qualified_SEER(df_boundary, eo3__Equipment_Type__c, EER):
coefficients = df_boundary['eo3__Equipment_Type__c'].str.startswith('iCoef').fillna(False)
df = df_boundary.loc[coefficients,:].dropna(axis=1)
iCoef0, iCoef1, iCoef2, iCoef3, iCoef4 = df_boundary.loc[coefficients,eo3__Equipment_Type__c].values
SEER = 1/(-(iCoef0-(iCoef1-iCoef4/EER))/iCoef2+iCoef3)
# display(iCoef0,iCoef1,iCoef2, iCoef3, iCoef4)
return round(SEER,1)
Test the qualified_SEER formula
display(labels[3])
qualified_SEER(df_boundary,labels[3], 10.9)
'MN - RTUS - 20 - 63.33 tons'
12.0
# SEER_test = pd.DataFrame(data={'EER': np.arange(9,14,0.1)})
# SEER_test['SEER'] = SEER_test['EER'].apply(lambda x: qualified_SEER(df_boundary,dx_unit,x))
# SEER_test
# SEER_test.plot(x='EER', y='SEER')
# # plt.xlim(9.5,10)
# plt.show()
qualified_SEER(df_boundary,dx_unit,10)
26.4
dx_unit = labels[3]
SEERs = [qualified_SEER(df_boundary,dx_unit,eer) for eer in np.linspace(9,14)]
plt.plot(np.linspace(9,12),SEERs, linewidth=5)
plt.ylim(5,25)
plt.ylabel('SEER')
plt.xlabel('EER')
plt.title(f'Pass/Fail Boundary Curve - {labels[3]}')
plt.fill_between(np.linspace(9,12),SEERs,y2=50, alpha=0.2, color='green', label='Qualified')
plt.grid(axis='both')
plt.annotate('Constant ARR $',xy=(10,5.5),xycoords='data',xytext=(10,5.5), rotation=-35, fontsize = 14)
plt.xlim(9.3,12)
# Min_SEER = df.loc[df['dx_units_category']==dx_unit,'Min_SEER__c'].unique()[0]
# Min_EER = df.loc[df['dx_units_category']==dx_unit,'Min_EER__c'].unique()[0]
# plt.annotate('Minimum Qualifed ARR', xy=(Min_EER, Min_SEER), xycoords='data',
# xytext=(Min_EER*1.12, Min_SEER*0.85), textcoords='data',
# arrowprops=dict(facecolor='black', shrink=0.05),
# horizontalalignment='right', verticalalignment='top')
plt.show()
df_min_arr_ton = pd.read_excel(file_calculator, sheet_name='Calculator')
# df_min_arr_ton.info()
df_min_arr_ton = xu.locate_table_by_header(df_min_arr_ton, header="Minimum Qualifying ARR Values using 2021 'static' Rules",
rows_after_header=25)
# df_min_arr_ton.head(20)
columns_idx = list(df_min_arr_ton.iloc[:,0] == "eo3__Equipment_Type__c").index(True)
# Set table column names to match eo3__Equipment_Type__c names
df_min_arr_ton.columns = df_min_arr_ton.iloc[columns_idx,:].values
# df_min_arr_ton
coefficients = df_min_arr_ton['eo3__Equipment_Type__c'].str.startswith('Minimum Qualified ARR').fillna(False)
df_min_arr_ton = df_min_arr_ton.loc[coefficients,labels].dropna(axis=1)
df_min_arr_ton
| MN - RTUS < 5.42 tons | MN - RTUS - 5.42 - 11.24 tons | MN - RTUS - 11.25 - 19.99 tons | MN - RTUS - 20 - 63.33 tons | MN - RTUS - 63.34 tons and above | |
|---|---|---|---|---|---|
| 17 | 13.006583 | 68.238041 | 73.00815 | 246.264688 | 315.472107 |
The Goal is to produce a table with EER vs SEER and a pass/fail status for each combination
df['Equipment SEER/IEER'].min()
8.4
# min_SEER = df_equipment_equipment_specs['SEER_Baseline__c'].min()
min_SEER = df['Equipment SEER/IEER'].min()
max_SEER = df['Equipment SEER/IEER'].max()
# min_EER = df_equipment_equipment_specs['EER_Baseline__c'].min()
min_EER = df['Equipment EER'].min()
# max_EER = df_equipment_equipment_specs['EER_Baseline__c'].max()
max_EER = df['Equipment EER'].max()
max_SEER
23.2
increment_size = 0.1
EER_index = np.arange(min_EER,max_EER+increment_size,increment_size)
# SEER_index = np.arange(min_SEER,max_SEER+increment_size,increment_size)
df_EER_SEER = pd.DataFrame(index=np.round(EER_index,1), columns=labels, data=0).rename_axis('EER').rename_axis(columns='eo3__Equipment_Type__c')
df_EER_SEER.head(4)
| eo3__Equipment_Type__c | MN - RTUS < 5.42 tons | MN - RTUS - 5.42 - 11.24 tons | MN - RTUS - 11.25 - 19.99 tons | MN - RTUS - 20 - 63.33 tons | MN - RTUS - 63.34 tons and above |
|---|---|---|---|---|---|
| EER | |||||
| 8.7 | 0 | 0 | 0 | 0 | 0 |
| 8.8 | 0 | 0 | 0 | 0 | 0 |
| 8.9 | 0 | 0 | 0 | 0 | 0 |
| 9.0 | 0 | 0 | 0 | 0 | 0 |
for col in df_EER_SEER.columns:
seers = np.array([qualified_SEER(df_boundary,col,eer) for eer in df_EER_SEER.index.values])
EER_Baseline__c = df_equipment.loc[df_equipment['eo3__Equipment_Type__c'] == col,'EER_Baseline__c'].values[0]
SEER_Baseline__c = df_equipment.loc[df_equipment['eo3__Equipment_Type__c'] == col,'SEER_Baseline__c'].values[0]
# Is the EER value higher than EER Baseline?
qualified_seers = np.where((df_EER_SEER.index.values>EER_Baseline__c),seers,99.9)
# Is the SEER value higher than SEER Baseline?
qualified_seers = np.where((qualified_seers>SEER_Baseline__c),qualified_seers,SEER_Baseline__c)
qualified_seers = np.where(qualified_seers!=99.9,qualified_seers,'Not Qualified')
df_EER_SEER[col] = qualified_seers
# df_EER_SEER.sample(5).sort_index()
df_EER_SEER.loc[9.5:12]
| eo3__Equipment_Type__c | MN - RTUS < 5.42 tons | MN - RTUS - 5.42 - 11.24 tons | MN - RTUS - 11.25 - 19.99 tons | MN - RTUS - 20 - 63.33 tons | MN - RTUS - 63.34 tons and above |
|---|---|---|---|---|---|
| EER | |||||
| 9.5 | Not Qualified | Not Qualified | Not Qualified | Not Qualified | Not Qualified |
| 9.6 | Not Qualified | Not Qualified | Not Qualified | Not Qualified | 67.2 |
| 9.7 | Not Qualified | Not Qualified | Not Qualified | Not Qualified | 48.1 |
| 9.8 | Not Qualified | Not Qualified | Not Qualified | Not Qualified | 37.6 |
| 9.9 | Not Qualified | Not Qualified | Not Qualified | 31.0 | 31.0 |
| 10.0 | Not Qualified | Not Qualified | Not Qualified | 26.4 | 26.4 |
| 10.1 | Not Qualified | Not Qualified | Not Qualified | 23.1 | 23.1 |
| 10.2 | Not Qualified | Not Qualified | Not Qualified | 20.6 | 20.6 |
| 10.3 | Not Qualified | Not Qualified | Not Qualified | 18.6 | 18.6 |
| 10.4 | Not Qualified | Not Qualified | Not Qualified | 16.9 | 16.9 |
| 10.5 | Not Qualified | Not Qualified | Not Qualified | 15.6 | 15.6 |
| 10.6 | Not Qualified | Not Qualified | Not Qualified | 14.5 | 14.5 |
| 10.7 | Not Qualified | Not Qualified | Not Qualified | 13.5 | 13.5 |
| 10.8 | Not Qualified | Not Qualified | Not Qualified | 12.7 | 12.7 |
| 10.9 | Not Qualified | Not Qualified | 13.6 | 12.0 | 12.0 |
| 11.0 | Not Qualified | Not Qualified | 12.8 | 11.4 | 11.4 |
| 11.1 | 13.3 | 13.7 | 12.1 | 10.8 | 10.8 |
| 11.2 | 13.0 | 12.9 | 11.5 | 10.3 | 10.3 |
| 11.3 | 13.0 | 12.2 | 11.0 | 9.9 | 9.9 |
| 11.4 | 13.0 | 11.6 | 11.0 | 9.9 | 9.6 |
| 11.5 | 13.0 | 11.2 | 11.0 | 9.9 | 9.6 |
| 11.6 | 13.0 | 11.2 | 11.0 | 9.9 | 9.6 |
| 11.7 | 13.0 | 11.2 | 11.0 | 9.9 | 9.6 |
| 11.8 | 13.0 | 11.2 | 11.0 | 9.9 | 9.6 |
| 11.9 | 13.0 | 11.2 | 11.0 | 9.9 | 9.6 |
| 12.0 | 13.0 | 11.2 | 11.0 | 9.9 | 9.6 |
# sns.set_context('talk')
# for dx_unit in ['MN - RTUS - 5.42 - 11.24 tons','MN - RTUS - 11.25 - 19.99 tons', 'MN - RTUS - 20 - 63.33 tons'][:1]:
# for dx_unit in labels[1:2]:
for dx_unit in labels[1:-1]:
SEER_max_display = 18
SEER_baseline = float(df_equipment.loc[df_equipment['eo3__Equipment_Type__c'] == dx_unit, 'SEER_Baseline__c'] )
EER_baseline = float(df_equipment.loc[df_equipment['eo3__Equipment_Type__c'] == dx_unit, 'EER_Baseline__c'])
Min_SEER = df.loc[df['dx_units_category']==dx_unit,'Min_SEER__c'].unique()[0]
Min_EER = df.loc[df['dx_units_category']==dx_unit,'Min_EER__c'].unique()[0]
sns.displot(data = df.loc[df['dx_units_category'] == dx_unit], x='Equipment EER',
y='Equipment SEER/IEER', kind='kde', height=7, aspect=1.7)
# plot min qualifying lines
plt.hlines(Min_SEER,9,13, linestyle='--', colors='red', label='Min SEER')
plt.vlines(Min_EER,9,SEER_max_display, linestyle='dotted', colors='red', label='Min EER')
# plot baseline EER/SEER lines
plt.hlines(SEER_baseline,9,13, linestyle='--', colors='gray', label='Baseline SEER')
plt.vlines(EER_baseline,9,SEER_max_display, linestyle='dotted', colors='gray', label='Baseline EER')
# calculate min qualifying boundary curve coordinates
eers = list(np.arange(EER_baseline,SEER_max_display,0.1))
seers = list(np.clip([qualified_SEER(df_boundary,dx_unit,eer) for eer in eers],SEER_baseline,SEER_max_display))
seers_vline = list(np.arange(qualified_SEER(df_boundary,dx_unit,EER_baseline),SEER_max_display,0.1))
eers_vline = [EER_baseline]*len(seers_vline)
eers = eers_vline + eers
seers = seers_vline + seers
# plot flexible min qualifying boundary curve
plt.plot(eers,seers, color='black', linewidth=3, label='Qualification Boundary')
# Fill right and above curved boundary with green
plt.fill_between(eers,seers,y2=SEER_max_display, alpha=0.3, color='green', label='Qualified')
# Fill left of curved boundary with yellow
plt.fill_betweenx(seers,eers,[EER_baseline]*len(seers), alpha=0.3, color='yellow', label='Code Compliant')
# titles, grids, and plot limits
plt.suptitle(f'Qualification Boundary for - {dx_unit} from AHRI database', y=1.05)
plt.xlim(9,13)
plt.ylim(9,SEER_max_display)
plt.grid(axis='both')
plt.legend(loc='upper left')
plt.margins(0.9)
plt.show()
equipment_ = df['dx_units_category']
EER_ = df['Equipment EER'].round(1)
SEER_ = df['Equipment SEER/IEER'].round(1)
qualified = []
flex_qualified_SEER = []
for equipment, EER, SEER in zip(equipment_, EER_, SEER_):
# print(EER, equipment)
if df_EER_SEER.loc[EER,equipment] == "Not Qualified":
qualified.append(False)
flex_qualified_SEER.append("EER too low")
continue
min_SEER = round(float(df_EER_SEER.loc[EER,equipment]),1)
if SEER > min_SEER:
qualified_ = True
else:
qualified_ = False
flex_qualified_SEER.append(min_SEER)
qualified.append(qualified_)
# sum(qualified), len(qualified), len(equipment_), len(flex_qualified_SEER)
df['qualified_SEER_(flex_criteria)'] = flex_qualified_SEER
df['qualified_(flex_criteria)'] = qualified
df[['dx_units_category','Equipment EER', 'Equipment SEER/IEER',
'qualified_EER', 'qualified_SEER', 'qualified_(standard_criteria)','qualified_SEER_(flex_criteria)', 'qualified_(flex_criteria)' ]]
| dx_units_category | Equipment EER | Equipment SEER/IEER | qualified_EER | qualified_SEER | qualified_(standard_criteria) | qualified_SEER_(flex_criteria) | qualified_(flex_criteria) | |
|---|---|---|---|---|---|---|---|---|
| 0 | MN - RTUS - 11.25 - 19.99 tons | 11.10 | 14.6 | True | True | True | 12.1 | True |
| 1 | MN - RTUS - 11.25 - 19.99 tons | 10.18 | 12.0 | False | False | False | EER too low | False |
| 2 | MN - RTUS - 11.25 - 19.99 tons | 8.99 | 11.6 | False | False | False | EER too low | False |
| 3 | MN - RTUS - 11.25 - 19.99 tons | 8.99 | 11.6 | False | False | False | EER too low | False |
| 4 | MN - RTUS - 11.25 - 19.99 tons | 10.00 | 11.6 | False | False | False | EER too low | False |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 14041 | MN - RTUS < 5.42 tons | 9.96 | 9.6 | False | False | False | EER too low | False |
| 14042 | MN - RTUS < 5.42 tons | 11.20 | 13.5 | True | False | False | 13.0 | True |
| 14043 | MN - RTUS < 5.42 tons | 11.20 | 13.5 | True | False | False | 13.0 | True |
| 14044 | MN - RTUS < 5.42 tons | 11.50 | 14.3 | True | True | True | 13.0 | True |
| 14045 | MN - RTUS < 5.42 tons | 11.70 | 14.3 | True | True | True | 13.0 | True |
14046 rows × 8 columns
(df['qualified_(flex_criteria)'] > df['qualified_(standard_criteria)']).sum()
1551
df_qualification_compare = df.groupby('dx_units_category').sum()\
[['qualified_(standard_criteria)','qualified_(flex_criteria)']]
df_qualification_compare.loc[labels[1:-1],:].plot(kind='bar', width=0.8, figsize=(8,3))
plt.legend(loc='lower center')
plt.ylabel('Quantity')
plt.xticks(labels=[x.split("RTUS - ")[-1] for x in labels[1:-1]], ticks=[0,1,2], rotation=35)
plt.title("Total Qualified AHRI Units - Standard vs Flexible criteria")
plt.grid(axis='y')
(df_qualification_compare.T.pct_change().round(3)*100).loc["qualified_(flex_criteria)",labels[1:-1]].plot(kind='bar', color='grey')
plt.ylabel("Percent Change")
plt.grid(axis='y')
plt.xticks(labels=[x.split("RTUS - ")[-1] for x in labels[1:-1]], ticks=[0,1,2], rotation=35)
plt.title("Qualified Units Percent Change - Flexible vs Standard criteria")
plt.show()
pd.options.display.max_rows = 200
df_history_summary = df_history.groupby('F_Equipment_Name_Proposed__c').agg({'Name':'count',
'I_Qty_Prop_Equip__c':sum,
'i_unit_cooling_tons_mc__c':'mean',
'I_EER_MC04__c':'mean',
'i_SEER_MC04__c':'mean',
'P_Cooling_Hrs_mc4__c':'mean'
}).loc[labels]
df_history_summary.rename(columns={'Name':'Participants'}, inplace=True)
df_history_summary
| Participants | I_Qty_Prop_Equip__c | i_unit_cooling_tons_mc__c | I_EER_MC04__c | i_SEER_MC04__c | P_Cooling_Hrs_mc4__c | |
|---|---|---|---|---|---|---|
| F_Equipment_Name_Proposed__c | ||||||
| MN - RTUS < 5.42 tons | 2240 | 3496.0 | 3.787759 | 12.304392 | 15.089196 | 600.486161 |
| MN - RTUS - 5.42 - 11.24 tons | 1114 | 1695.0 | 7.981293 | 12.172792 | 14.265934 | 621.456014 |
| MN - RTUS - 11.25 - 19.99 tons | 529 | 818.0 | 14.553025 | 12.100681 | 14.483043 | 786.449905 |
| MN - RTUS - 20 - 63.33 tons | 195 | 320.0 | 29.595949 | 11.595436 | 13.920656 | 820.092308 |
| MN - RTUS - 63.34 tons and above | 21 | 29.0 | 86.544286 | 11.080952 | 15.107143 | 1089.857143 |
df_history_summary['Percent_projected_change_quantity'] = (df_qualification_compare.T.pct_change()*100).round(3).loc['qualified_(flex_criteria)'].replace(100,np.nan)
df_history_summary.round(2)
| Participants | I_Qty_Prop_Equip__c | i_unit_cooling_tons_mc__c | I_EER_MC04__c | i_SEER_MC04__c | P_Cooling_Hrs_mc4__c | Percent_projected_change_quantity | |
|---|---|---|---|---|---|---|---|
| F_Equipment_Name_Proposed__c | |||||||
| MN - RTUS < 5.42 tons | 2240 | 3496.0 | 3.79 | 12.30 | 15.09 | 600.49 | NaN |
| MN - RTUS - 5.42 - 11.24 tons | 1114 | 1695.0 | 7.98 | 12.17 | 14.27 | 621.46 | 15.86 |
| MN - RTUS - 11.25 - 19.99 tons | 529 | 818.0 | 14.55 | 12.10 | 14.48 | 786.45 | 20.36 |
| MN - RTUS - 20 - 63.33 tons | 195 | 320.0 | 29.60 | 11.60 | 13.92 | 820.09 | 39.20 |
| MN - RTUS - 63.34 tons and above | 21 | 29.0 | 86.54 | 11.08 | 15.11 | 1089.86 | NaN |
Save output to disk
df_history_summary.round(2).to_csv('DX_Unit History Summary 2017-20.csv')
df_AHRI_passing_standard = df.loc[df['qualified_(standard_criteria)']].groupby('eo3__Equipment_Type__c').agg({'eo3__Equipment_Type__c':'count',
'Unit Size (Tons)':'mean',
'Equipment EER':'mean',
'Equipment SEER/IEER':'mean'
}).loc[labels[1:-1]].round(2)
df_AHRI_passing_standard.rename(columns={'eo3__Equipment_Type__c':'I_Qty_Prop_Equip__c'}, inplace=True)
df_AHRI_passing_standard
| I_Qty_Prop_Equip__c | Unit Size (Tons) | Equipment EER | Equipment SEER/IEER | |
|---|---|---|---|---|
| eo3__Equipment_Type__c | ||||
| MN - RTUS - 5.42 - 11.24 tons | 2408 | 7.91 | 12.15 | 15.34 |
| MN - RTUS - 11.25 - 19.99 tons | 2078 | 15.66 | 11.92 | 14.24 |
| MN - RTUS - 20 - 63.33 tons | 1885 | 30.89 | 11.15 | 14.88 |
df_AHRI_passing_flex = df.loc[df['qualified_(flex_criteria)']].groupby('eo3__Equipment_Type__c').agg({'eo3__Equipment_Type__c':'count',
'Unit Size (Tons)':'mean',
'Equipment EER':'mean',
'Equipment SEER/IEER':'mean'
}).loc[labels[1:-1]].round(2)
df_AHRI_passing_flex.rename(columns={'eo3__Equipment_Type__c':'I_Qty_Prop_Equip__c'}, inplace=True)
df_AHRI_passing_flex
| I_Qty_Prop_Equip__c | Unit Size (Tons) | Equipment EER | Equipment SEER/IEER | |
|---|---|---|---|---|
| eo3__Equipment_Type__c | ||||
| MN - RTUS - 5.42 - 11.24 tons | 2790 | 7.92 | 12.02 | 15.15 |
| MN - RTUS - 11.25 - 19.99 tons | 2501 | 15.73 | 11.76 | 14.17 |
| MN - RTUS - 20 - 63.33 tons | 2624 | 31.98 | 11.03 | 14.92 |
df_std_to_flex_pct_change = df_AHRI_passing_flex.div(df_AHRI_passing_standard)
df_std_to_flex_pct_change.round(2)
| I_Qty_Prop_Equip__c | Unit Size (Tons) | Equipment EER | Equipment SEER/IEER | |
|---|---|---|---|---|
| eo3__Equipment_Type__c | ||||
| MN - RTUS - 5.42 - 11.24 tons | 1.16 | 1.00 | 0.99 | 0.99 |
| MN - RTUS - 11.25 - 19.99 tons | 1.20 | 1.00 | 0.99 | 1.00 |
| MN - RTUS - 20 - 63.33 tons | 1.39 | 1.04 | 0.99 | 1.00 |
Save output to disk
# df_AHRI_passing_standard.to_csv('DX_AHRI_passing_standard.csv')
# df_AHRI_passing_flex.to_csv('DX_AHRI_passing_flex.csv')
# df_std_to_flex_pct_change.to_csv('DX_Unit Standard to Flex pct change.csv')